﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace PromotionApp
{
    /// <summary>
    /// Summary description for PoximoService
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class PoximoService : System.Web.Services.WebService
    {

        [WebMethod]
        public XmlDocument Promotion(string CusLat, string CusLong, string Distance)
        {
            XmlDocument myDatas = new XmlDocument();
            SqlConnection SqlConnection =
                        new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            SqlCommand SqlCommand = SqlConnection.CreateCommand();
            SqlCommand.CommandText =
              "Select Shop_ID,Shop_Name,Shop_Logo,Location_Name,Location_Detail,Location_Lat,Location_Long,ShopType_Name," +
              "6371*2*ASIN(SQRT(POWER(SIN(((@CusLat)-(Location_Lat))*pi()/180/2 ), 2) + COS((@CusLat)*pi()/180)*COS((Location_Lat) *pi()/180)*POWER(SIN(((@CusLong)-(Location_Long))*pi()/180/2 ),2)))as Distance,Location_Shop_ID " +
              "From Shop T1 " +
              "inner join Location T2 on  T1.Shop_ID = T2.Location_Shop_ID " +
              "inner join ShopType T5 on  T1.Shop_ShopType_ID = T5.ShopType_ID " +
              "where 6371*2*ASIN(SQRT(POWER(SIN(((@CusLat)-(Location_Lat))*pi()/180/2 ), 2) + COS((@CusLat)*pi()/180)*COS((Location_Lat) *pi()/180)*POWER(SIN(((@CusLong)-(Location_Long))*pi()/180/2 ),2))) <= @Distance ";
            SqlCommand.Parameters.Add("@CusLat", SqlDbType.VarChar).Value = CusLat;
            SqlCommand.Parameters.Add("@CusLong", SqlDbType.VarChar).Value = CusLong;
            SqlCommand.Parameters.Add("@Distance", SqlDbType.VarChar).Value = Distance;
            SqlDataAdapter SqlDataAdapter = new SqlDataAdapter();
            SqlDataAdapter.SelectCommand = SqlCommand;
            DataSet ds = new DataSet();
            SqlConnection.Open();
            ds.DataSetName = "Place";
            SqlDataAdapter.Fill(ds, "Shop");

            SqlCommand.CommandText =
              "Select Deal_ID,Deal_Name,Deal_Detail,Deal_Code,Deal_Image, " +
              "CONVERT(VARCHAR(11),DealPeriod_StartTime,113) as StartTime, CONVERT(VARCHAR(11),DealPeriod_EndTime,113)as EndTime,Deal_Shop_ID " +
              "From Shop T1 " +
              "inner join Location T2 on  T1.Shop_ID = T2.Location_Shop_ID " +
              //"inner join Deal T3 on T2.Location_ID = T3.Deal_Location_ID " +
              "inner join DealLocation T6 on T2.Location_ID = T6.DealLocation_Location_ID " +
              "inner join Deal T3 on T3.Deal_ID = T6.DealLocation_Deal_ID " +
              "inner join DealPeriod T4 on T3.Deal_ID = T4.DealPeriod_Deal_ID " +
              "inner join ShopType T5 on  T1.Shop_ShopType_ID = T5.ShopType_ID " +
              "where 6371*2*ASIN(SQRT(POWER(SIN(((@CusLat)-(Location_Lat))*pi()/180/2 ), 2) + COS((@CusLat)*pi()/180)*COS((Location_Lat) *pi()/180)*POWER(SIN(((@CusLong)-(Location_Long))*pi()/180/2 ),2))) <= @Distance ";

            SqlDataAdapter.SelectCommand = SqlCommand;
            SqlDataAdapter.Fill(ds, "Deal");

            //DataColumn dcShop, dcDeal;
            //dcShop = ds.Tables["Shop"].Columns["Location_ID"];
            //dcDeal = ds.Tables["Deal"].Columns["Deal_Location_ID"];

            //DataRelation dl = new DataRelation("shops", dcShop, dcDeal);
            //ds.Relations.Add(dl);
            //dl.Nested = true;

            SqlConnection.Close();

            //if (ds.Tables["Shop"].Rows.Count != 0)
            //    myDatas.LoadXml(ds.GetXml());

            DataTable dtShop = new DataTable();
            DataTable dtDeal = new DataTable();
            dtShop = ds.Tables[0];
            dtDeal = ds.Tables[1];
            DataRow dr;

            // XML declaration
            XmlNode declaration = myDatas.CreateXmlDeclaration("1.0", "UTF-8", null);
            myDatas.AppendChild(declaration);

            // Root element: Places
            XmlElement root = myDatas.CreateElement("Places");
            myDatas.AppendChild(root);

            for (int i = 0; i <= dtShop.Rows.Count - 1; i++)
            {
                dr = dtShop.Rows[i];

                XmlElement child = myDatas.CreateElement("Shops");
                XmlNode Shop_ID = myDatas.CreateElement("ShopID");
                XmlNode cdataShopID = myDatas.CreateCDataSection(dr[0].ToString());
                Shop_ID.AppendChild(cdataShopID);
                child.AppendChild(Shop_ID);
                root.AppendChild(child);

                XmlNode Shop_Name = myDatas.CreateElement("ShopName");
                XmlNode cdataShopName = myDatas.CreateCDataSection(dr[1].ToString());
                Shop_Name.AppendChild(cdataShopName);
                child.AppendChild(Shop_Name);
                root.AppendChild(child);

                XmlNode Shop_Logo = myDatas.CreateElement("ShopLogo");
                XmlNode cdataShopLogo = myDatas.CreateCDataSection(dr[2].ToString());
                Shop_Logo.AppendChild(cdataShopLogo);
                child.AppendChild(Shop_Logo);
                root.AppendChild(child);

                XmlNode Location_Name = myDatas.CreateElement("LocationName");
                XmlNode cdataLocationName = myDatas.CreateCDataSection(dr[3].ToString());
                Location_Name.AppendChild(cdataLocationName);
                child.AppendChild(Location_Name);
                root.AppendChild(child);

                XmlNode Location_Detail = myDatas.CreateElement("LocationDetail");
                XmlNode cdataLocationDetail = myDatas.CreateCDataSection(dr[4].ToString());
                Location_Detail.AppendChild(cdataLocationDetail);
                child.AppendChild(Location_Detail);
                root.AppendChild(child);

                XmlNode Location_Lat = myDatas.CreateElement("LocationLat");
                XmlNode cdataLocationLat = myDatas.CreateCDataSection(dr[5].ToString());
                Location_Lat.AppendChild(cdataLocationLat);
                child.AppendChild(Location_Lat);
                root.AppendChild(child);

                XmlNode Location_Long = myDatas.CreateElement("LocationLong");
                XmlNode cdataLocationLong = myDatas.CreateCDataSection(dr[6].ToString());
                Location_Long.AppendChild(cdataLocationLong);
                child.AppendChild(Location_Long);
                root.AppendChild(child);

                XmlNode ShopType_Name = myDatas.CreateElement("ShopTypeName");
                XmlNode cdataShopTypeName = myDatas.CreateCDataSection(dr[7].ToString());
                ShopType_Name.AppendChild(cdataShopTypeName);
                child.AppendChild(ShopType_Name);
                root.AppendChild(child);

                XmlNode Shop_Distance = myDatas.CreateElement("Distance");
                XmlNode cdataDistance = myDatas.CreateCDataSection(dr[8].ToString());
                Shop_Distance.AppendChild(cdataDistance);
                child.AppendChild(Shop_Distance);
                root.AppendChild(child);

                string LocationShopID = dr[9].ToString();

                for (int a = 0; a <= dtDeal.Rows.Count - 1; a++)
                {
                    dr = dtDeal.Rows[a];
                    string DealShopID = dr[7].ToString();
                    //if (LocationID == DealLocationID)
                    if (LocationShopID == DealShopID)
                    {
                        XmlNode subchild = myDatas.CreateElement("Promotion");
                        XmlNode Deal_ID = myDatas.CreateElement("DealID");
                        XmlNode cdataDealID = myDatas.CreateCDataSection(dr[0].ToString());
                        Deal_ID.AppendChild(cdataDealID);
                        subchild.AppendChild(Deal_ID);
                        child.AppendChild(subchild);

                        XmlNode Deal_Name = myDatas.CreateElement("DealName");
                        XmlNode cdataDealName = myDatas.CreateCDataSection(dr[1].ToString());
                        Deal_Name.AppendChild(cdataDealName);
                        subchild.AppendChild(Deal_Name);
                        child.AppendChild(subchild);

                        XmlNode Deal_Detail = myDatas.CreateElement("DealDetail");
                        XmlNode cdataDealDetail = myDatas.CreateCDataSection(dr[2].ToString());
                        Deal_Detail.AppendChild(cdataDealDetail);
                        subchild.AppendChild(Deal_Detail);
                        child.AppendChild(subchild);

                        XmlNode Deal_Code = myDatas.CreateElement("DealCode");
                        XmlNode cdataDealCode = myDatas.CreateCDataSection(dr[3].ToString());
                        Deal_Code.AppendChild(cdataDealCode);
                        subchild.AppendChild(Deal_Code);
                        child.AppendChild(subchild);

                        XmlNode Deal_Image = myDatas.CreateElement("DealImage");
                        XmlNode cdataDealImage = myDatas.CreateCDataSection(dr[4].ToString());
                        Deal_Image.AppendChild(cdataDealImage);
                        subchild.AppendChild(Deal_Image);
                        child.AppendChild(subchild);

                        XmlNode Start_Time = myDatas.CreateElement("StartTime");
                        XmlNode cdataStartTime = myDatas.CreateCDataSection(dr[5].ToString());
                        Start_Time.AppendChild(cdataStartTime);
                        subchild.AppendChild(Start_Time);
                        child.AppendChild(subchild);

                        XmlNode End_Time = myDatas.CreateElement("EndTime");
                        XmlNode cdataEndTime = myDatas.CreateCDataSection(dr[6].ToString());
                        End_Time.AppendChild(cdataEndTime);
                        subchild.AppendChild(End_Time);
                        child.AppendChild(subchild);
                    }
                }

            }
            return myDatas;
        }

        [WebMethod]
        public string Customer(string CusLat, string CusLong, string DealID)
        {
            int LastCusID = 0;

            SqlConnection SqlConnection =
                        new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            SqlCommand SqlCommand = SqlConnection.CreateCommand();
            SqlConnection.Open();
            SqlCommand.Connection = SqlConnection;

            SqlCommand.CommandText =
              "Insert into Customer (Customer_Lat, Customer_Long, Customer_CreateDate)" +
              "VALUES (@CusLat,@CusLong,getdate());Select CAST(Scope_Identity() AS int)";
            SqlCommand.Parameters.Add("@CusLat", SqlDbType.VarChar).Value = CusLat;
            SqlCommand.Parameters.Add("@CusLong", SqlDbType.VarChar).Value = CusLong;
            SqlCommand.CommandType = CommandType.Text;

            LastCusID = (int)SqlCommand.ExecuteScalar();

            SqlCommand.CommandText =
              "Insert into PushedDeal (PushedDeal_Customer_ID, PushedDeal_CreateDate, PushedDeal_Deal_ID)" +
              "VALUES (@LastCusID,getdate(),@DealID)";
            SqlCommand.Parameters.Add("@DealID", SqlDbType.VarChar).Value = DealID;
            SqlCommand.Parameters.Add("@LastCusID", SqlDbType.Int).Value = LastCusID;
            SqlCommand.CommandType = CommandType.Text;
            SqlCommand.ExecuteNonQuery();

            SqlConnection.Close();

            return CusLat;
        }

    }
}
